package com.boil.util.office;

import com.boil.util.Constants;
import com.boil.util.FileUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Map;

public class ExcelUtils {
    /**
     * 将 Excel 文件 xlsx 格式转换为 Html。
     *
     * @param excelXls Excel 文件
     * @param htmlPath Html 路径
     */
    public static void xlsx2Html(File excelXls, String htmlPath) throws Exception {
        InputStream is = null;

        try {
            is = new FileInputStream(excelXls);

            XSSFWorkbook wb = new XSSFWorkbook(is);
            StringBuffer sb = new StringBuffer();
            Sheet sheet = wb.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            Map<String, String> map[] = getRowSpanColSpanMap(sheet);

            sb.append("<html><head>");
            sb.append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">");
            // sb.append("<meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0, maximum-scale=1.0\">");
            sb.append("</head><body>");
            sb.append("<table style='border-collapse:collapse;'  width='100%'>");

            XSSFRow row = null;
            XSSFCell cell = null;

            for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) {
                row = (XSSFRow) sheet.getRow(rowNum);

                if (row == null) {
                    sb.append("<tr><td style='" + getNullCellBorderStyle() + "' > &nbsp;</td></tr>");

                    continue;
                }

                sb.append("<tr>");

                int lastColNum = row.getLastCellNum();

                for (int colNum = 0; colNum < lastColNum; colNum++) {
                    cell = row.getCell(colNum);

                    if (cell == null) {
                        sb.append("<td style='" + getNullCellBorderStyle() + "'>&nbsp;</td>");

                        continue;
                    }

                    String stringValue = getCellValue(cell);

                    if (map[0].containsKey(rowNum + "," + colNum)) {
                        String pointString = map[0].get(rowNum + "," + colNum);

                        map[0].remove(rowNum + "," + colNum);

                        int bottomeRow = Integer.valueOf(pointString.split(",")[0]);
                        int bottomeCol = Integer.valueOf(pointString.split(",")[1]);
                        int rowSpan = bottomeRow - rowNum + 1;
                        int colSpan = bottomeCol - colNum + 1;

                        sb.append("<td rowspan= '" + rowSpan + "' colspan= '" + colSpan + "' ");

                    } else if (map[1].containsKey(rowNum + "," + colNum)) {
                        map[1].remove(rowNum + "," + colNum);

                        continue;

                    } else {
                        sb.append("<td ");
                    }

                    //获取样式的内容
                    XSSFCellStyle cellStyle = cell.getCellStyle();

                    if (cellStyle != null) {
                        short alignment = cellStyle.getAlignment();
                        sb.append("align='" + convertAlignToHtml(alignment) + "' ");

                        short verticalAlignment = cellStyle.getVerticalAlignment();

                        sb.append("valign='" + convertVerticalAlignToHtml(verticalAlignment) + "' ");

                        XSSFFont xf = cellStyle.getFont();
                        short boldWeight = xf.getBoldweight();
                        XSSFColor xc = xf.getXSSFColor();

                        sb.append("style='");

                        String fontColorStr = ColorUtil.convertColorToHex(xc);
                        int columnWidth = sheet.getColumnWidth(cell.getColumnIndex());

                        sb.append("width:" + columnWidth + "px;");

                        if (fontColorStr != null && !"".equals(fontColorStr.trim())) {
                            sb.append("color:" + fontColorStr + ";"); // 字体颜色
                        }

                        XSSFColor bgColor = cellStyle.getFillForegroundXSSFColor();
                        String bgColorStr = ColorUtil.convertColorToHex(bgColor);


                        if (bgColorStr != null && !"".equals(bgColorStr.trim())) {
                            sb.append("background-color:" + bgColorStr + ";"); // 背景颜色
                        }

                        sb.append(getBorderStyle(0, cellStyle.getBorderTop(), cellStyle.getTopBorderXSSFColor()));
                        sb.append(getBorderStyle(1, cellStyle.getBorderRight(), cellStyle.getRightBorderXSSFColor()));
                        sb.append(getBorderStyle(2, cellStyle.getBorderBottom(), cellStyle.getBottomBorderXSSFColor()));
                        sb.append(getBorderStyle(3, cellStyle.getBorderLeft(), cellStyle.getLeftBorderXSSFColor()));

                        sb.append("font-weight:" + boldWeight + ";"); // 字体加粗
                        sb.append("font-size: " + xf.getFontHeight() / 2.5 + "%;"); // 字体大小
                        sb.append("' ");
                    }

                    sb.append(">");

                    if (stringValue == null || "".equals(stringValue.trim())) {
                        sb.append(" &nbsp; ");
                    } else {
                        // 将ascii码为160的空格转换为html下的空格（&nbsp;）
                        sb.append(stringValue.replace(String.valueOf((char) 160), "&nbsp;"));
                    }

                    sb.append("</td>");
                }

                sb.append("</tr>");
            }

            sb.append("</table>");
            sb.append("</body></html>");

            String ma = sb.toString().replace("00.00%", "");

            writeFile(ma, String.format("%s/%s%s%s", htmlPath, FileUtils.getFileNameNoSuffix(excelXls.getName()), Constants.POINT_CHAR, Constants.SUFFIX_HTML));
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (is != null) {
                    is.close();
                }
            } catch (Exception e) {
            }
        }
    }

    private static Map<String, String>[] getRowSpanColSpanMap(Sheet sheet) {
        Map<String, String> map0 = new HashMap<String, String>();
        Map<String, String> map1 = new HashMap<String, String>();

        int mergedNum = sheet.getNumMergedRegions();

        CellRangeAddress range = null;

        for (int i = 0; i < mergedNum; i++) {

            range = sheet.getMergedRegion(i);

            int topRow = range.getFirstRow();
            int topCol = range.getFirstColumn();
            int bottomRow = range.getLastRow();
            int bottomCol = range.getLastColumn();

            map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol);

            int tempRow = topRow;

            while (tempRow <= bottomRow) {
                int tempCol = topCol;

                while (tempCol <= bottomCol) {
                    map1.put(tempRow + "," + tempCol, "");

                    tempCol++;
                }

                tempRow++;
            }

            map1.remove(topRow + "," + topCol);
        }

        Map[] map = {map0, map1};

        return map;
    }

    static String[] bordesr = {"border-top:", "border-right:", "border-bottom:", "border-left:"};
    static String[] borderStyles = {"solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid", "solid", "solid", "solid", "solid"};

    private static String getBorderStyle(int b, short s, XSSFColor t) {
        if (s == 0) {
            return bordesr[b] + borderStyles[s] + "#d0d7e5 1px;";
        }

        String borderColorStr = ColorUtil.convertColorToHex(t);
        borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000" : borderColorStr;

        return bordesr[b] + borderStyles[s] + borderColorStr + " 1px;";
    }

    private static String getNullCellBorderStyle() {
        return "border: #d0d7e5 1px 1px 1px 1px;";
    }

    private static String getCellValue(XSSFCell cell) {
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC:
                DecimalFormat format = new DecimalFormat();
                format.applyPattern("0.00%");
                return format.format(cell.getNumericCellValue());

            case XSSFCell.CELL_TYPE_STRING:
                return cell.getStringCellValue();

            case XSSFCell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();

            default:
                return "";
        }
    }

    private static String convertAlignToHtml(short alignment) {
        String align = "left";

        switch (alignment) {
            case XSSFCellStyle.ALIGN_LEFT:
                align = "left";
                break;
            case XSSFCellStyle.ALIGN_CENTER:
                align = "center";
                break;
            case XSSFCellStyle.ALIGN_RIGHT:
                align = "right";
                break;

            default:
                break;
        }

        return align;
    }

    private static String convertVerticalAlignToHtml(short verticalAlignment) {
        String valign = "middle";

        switch (verticalAlignment) {
            case XSSFCellStyle.VERTICAL_BOTTOM:
                valign = "bottom";
                break;
            case XSSFCellStyle.VERTICAL_CENTER:
                valign = "center";
                break;
            case XSSFCellStyle.VERTICAL_TOP:
                valign = "top";
                break;
            default:
                break;
        }

        return valign;
    }

    private static final String encoding = "UTF-8";

    private static void writeFile(String content, String path) throws Exception {
        FileOutputStream fileOutputStream = null;
        OutputStreamWriter outputStreamWriter = null;
        BufferedWriter bufferedWriter = null;

        try {
            File file = new File(path);

            fileOutputStream = new FileOutputStream(file);
            outputStreamWriter = new OutputStreamWriter(fileOutputStream, encoding);
            bufferedWriter = new BufferedWriter(outputStreamWriter);

            bufferedWriter.write(content);
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (bufferedWriter != null) {
                    bufferedWriter.close();
                }

                if (fileOutputStream != null) {
                    fileOutputStream.close();
                }

                if (fileOutputStream != null) {
                    fileOutputStream.close();
                }
            } catch (IOException ie) {
            }
        }
    }
}